package service;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;



import pojo.dishes;
import pojo.dishesType;




import base.BaseService;

public class DishesService extends BaseService {
	public class DishesRowMapper implements RowMapper<dishes> {
		
	  	   public dishes mapRow(ResultSet rs, int rownum) throws SQLException {
	  
		dishes inf = new dishes();
		inf.setId(rs.getInt("id"));
		inf.setName(rs.getString("name"));
		inf.setType(rs.getInt("type")); 
		inf.setQuality(rs.getString("quality"));
		inf.setPicture(rs.getString("picture"));
		inf.setDetail(rs.getString("detail"));
		inf.setSale(rs.getInt("sale"));
		inf.setPrice(rs.getFloat("price"));
		inf.setOrderNum(rs.getInt("orderNum"));
		
		return inf;
		
}
	}
	//获取菜品总数
	public int getALL(String where){
		String sql = "SELECT count(*) FROM dishes "+where;
		
		int count1 = this.jdbcTemplate.queryForInt(sql);
		
		return count1;
		
	}
	//菜品列表（后台）
	public List<dishes> getDishes(int pageno,int pagesize,String where){
		@SuppressWarnings("unused")
		class RowMapperss implements org.springframework.jdbc.core.RowMapper<dishes> {
			 public dishes mapRow(ResultSet rs, int rownum) throws SQLException {

				 dishes inf = new dishes();
					inf.setId(rs.getInt("id"));
					inf.setName(rs.getString("name"));
					inf.setType(rs.getInt("type")); 
					inf.setQuality(rs.getString("quality"));
					inf.setPicture(rs.getString("picture"));
					inf.setDetail(rs.getString("detail"));
					inf.setSale(rs.getInt("sale"));
					inf.setPrice(rs.getFloat("price"));
									
					return inf;
							}
					}			
		String sql = "SELECT * from dishes "+where+" limit "+(pageno-1)*pagesize+","+pagesize;
		return jdbcTemplate.query(sql,new RowMapperss());
}
	//根据id获取菜品信息
	public dishes getDishesDetail(int id){
		@SuppressWarnings("unused")
		class RowMapper implements org.springframework.jdbc.core.RowMapper<dishes> {
			 public dishes mapRow(ResultSet rs, int rownum) throws SQLException {

				 dishes inf = new dishes();
					inf.setId(rs.getInt("id"));
					inf.setName(rs.getString("name"));
					inf.setType(rs.getInt("type")); 
					inf.setQuality(rs.getString("quality"));
					inf.setPicture(rs.getString("picture"));
					inf.setDetail(rs.getString("detail"));
					inf.setSale(rs.getInt("sale"));
					inf.setPrice(rs.getFloat("price"));
									
					return inf;
							}
					}		
		
		String sql="select * from dishes where id=?";
		dishes de = null;
		
		try{
			de= jdbcTemplate.queryForObject(sql, new RowMapper(),id);
		
		}catch(EmptyResultDataAccessException e){
			
			}

		return de;
	}
	//更新菜品信息
	public void getUpdateByid(String name,int type,String quality,String picture,String detail,int sale,float price,int id){
		
		String sql ="update dishes set name=?,type=?,quality=?,picture=?,detail=?,sale=?,price=? where id=?";
		
		 jdbcTemplate.update(sql,name,type,quality,picture,detail,sale,price,id);
		}
	//删除菜品
	 public int DeleteD(int id){
			
			String sql ="delete from dishes where id=?";
			
			return jdbcTemplate.update(sql,id);
			
	 }	
	//获取菜品类型列表
	 public List<dishesType> getDishesType(){
		 class RowMapper implements org.springframework.jdbc.core.RowMapper<dishesType> {
			 public dishesType mapRow(ResultSet rs, int rownum) throws SQLException {

				 dishesType dt = new dishesType();
					dt.setId(rs.getInt("id"));
					dt.setName(rs.getString("name"));
									
					return dt;
							}
					}			
			String sql = "SELECT * from dishes_type ";
			return jdbcTemplate.query(sql,new RowMapper());
			
	}
	 //获取菜品类型名称
	 public dishesType getType(int id){
		 class RowMapper implements org.springframework.jdbc.core.RowMapper<dishesType> {
			 public dishesType mapRow(ResultSet rs, int rownum) throws SQLException {

				 dishesType dt = new dishesType();
					dt.setId(rs.getInt("id"));
					dt.setName(rs.getString("name"));
									
					return dt;
							}
					}			
			String sql = "SELECT * from dishes_type where id=?";
			return jdbcTemplate.queryForObject(sql,new RowMapper(),id);
	 }
	 //添加菜品
	 public int add(String name,int type,String quality,String picture,String detail,int sale,float price ){
			
			String sql ="insert into dishes(name,type,quality,picture,detail,sale,price) values(?,?,?,?,?,?,?)";
			
			return jdbcTemplate.update(sql,name,type,quality,picture,detail,sale,price);
		}
	 //统计菜品预订数量
	 public List<dishes> getCount(){

		class RowMapper implements org.springframework.jdbc.core.RowMapper<dishes> {
			 public dishes mapRow(ResultSet rs, int rownum) throws SQLException {

				 dishes d = new dishes();
					d.setType(rs.getInt("type"));
					d.setName(rs.getString("name"));
					d.setOrderNum(rs.getInt("orderNum"));
					d.setPrice(rs.getFloat("price"));	
					
					return d;
							}
					}		
		 
		 
		 
			String sql = "select  dishes.type,dishes.`name`,ordernum,dishes.price from dishes left join( select count(*) as ordernum,orderdetail.dishesName from orderdetail where orderdetail.orderid in (select id  from `order` where `order`.tradeStatus=4) group by dishesname) as a on dishes.`name`=a.dishesName order by dishes.type";
			return jdbcTemplate.query(sql,new RowMapper());
	}
	 //根据菜品类型统计该类型个数
	 public int getAllByType( int type){
			String sql = "SELECT count(*) FROM dishes where type="+type+"";
			
			int count1 = this.jdbcTemplate.queryForInt(sql);
			
			return count1;
			
		}
	 //根据菜品类型获取菜品列表
		public List<dishes> getDishesByType(int pageno,int pagesize,int type,String orderby){
			String sql = "select * from dishes  LEFT JOIN (SELECT COUNT(*) as ordernum,orderdetail.dishesName from orderdetail  join `order` on `order`.id = orderdetail.orderId where dishesName in (select dishesName from dishes where type=1 )and `order`.tradeStatus=4 GROUP BY orderdetail.dishesName) as a on dishes.`name`=a.dishesName where dishes.type="+type+" "+orderby+" limit "+(pageno-1)*pagesize+","+pagesize;
			return jdbcTemplate.query(sql,new DishesRowMapper());
	}
		//根据用户输入的菜品名模糊查询
		public List<dishes> selectdishes(String dishesname,int pageno,int pagesize){
		@SuppressWarnings("unused")
		class RowMapperss implements org.springframework.jdbc.core.RowMapper<dishes> {
			 public dishes mapRow(ResultSet rs, int rownum) throws SQLException {

				 dishes inf = new dishes();
					inf.setId(rs.getInt("id"));
					inf.setName(rs.getString("name"));
					inf.setType(rs.getInt("type")); 
					inf.setQuality(rs.getString("quality"));
					inf.setPicture(rs.getString("picture"));
					inf.setDetail(rs.getString("detail"));
					inf.setSale(rs.getInt("sale"));
					inf.setPrice(rs.getFloat("price"));
									
					return inf;
							}
					}		
			String sql = "SELECT * FROM dishes where dishes.name like '%"+dishesname+"%' limit "+(pageno-1)*pagesize+","+pagesize;;
			
			 return jdbcTemplate.query(sql,new RowMapperss());
	}
		 public int getselectNum(String dishesname){
				String sql = "SELECT count(*) FROM dishes where name like '%"+dishesname+"%'";
				
				int count1 = this.jdbcTemplate.queryForInt(sql);
				
				return count1;
				
			}
		
}
